package com.mini.repository;

import com.mini.model.BusinessRequirementPlanBaseBean;
import com.mini.model.BusinessRequirementPlanTableAndForm;
import com.mini.model.RequirementPlanBaseTableForm;
import feign.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * 项目名称：traditional-business
 * 类 名 称：BusinessRequirementPlanBaseTableFormRepository
 * 类 描 述：TODO
 * 创建时间：2020/12/21 下午8:30
 * 创 建 人：zhanghuiying
 */
@Repository
public interface BusinessRequirementPlanBaseTableFormRepository extends JpaRepository<RequirementPlanBaseTableForm,Long>,JpaSpecificationExecutor<RequirementPlanBaseTableForm> {

    @Query(value = "SELECT\n" +
            "\t f.id,\n" +
            "\t b.quantum_army_group_id,\n" +
            "\t b.card_count,\n" +
            "\t b.end_time,\n" +
            "\t b.receiving_residue,\n" +
            "\t b.pattern,\n" +
            "\t t.project_id,\n" +
            "\t f.channel_code,\n" +
            "\t f.province,\n" +
            "\t n.need_count,\n" +
            "\t f.need_id,\n" +
            "\t f.allot_count \n" +
            "FROM\n" +
            "\t business_requirement_plan_base b\n" +
            "\t LEFT JOIN business_requirement_plan_table t ON b.id = t.base_id\n" +
            "\t LEFT JOIN business_requirement_plan_form f ON t.id = f.table_id\n" +
            "\t LEFT JOIN business_requirement_plan_need n ON f.need_id = n.id \n" +
            "WHERE\n" +
            "\t b.quantum_army_group_id = ?1 \n" +
            "\t AND b.plan_date = ?2 \n" +
            "\t AND t.table_delete = 0 \n" +
            "\t AND f.form_delete = 0 \n" +
            "\t AND n.need_delete =0"
            ,nativeQuery = true)
    List<RequirementPlanBaseTableForm> findBaseTableForm(Long quantumArmyGroupId, Date planDate);
}
